import json
import math
from random import random

first_name = "李、王、张、刘、陈、杨、赵、黄、周、吴、徐、孙、胡、朱、高、林、何、郭、马、罗、梁、宋、郑、谢、韩、唐、欧阳、东方、董、" \
             "萧、程、曹、袁、邓、许、傅、沈、曾、彭、吕、苏、卢、蒋、蔡、贾、丁、魏、薛、叶、阎、余、潘、杜、戴、夏、钟、汪、田、" \
             "任、姜、范、方、石、姚、谭、廖、邹、熊、金、陆、郝、孔、白 "
last_name = "睿、浩、博、瑞、昊、悦、妍、涵、玥、蕊、子、梓、浩、宇、俊、轩、宇、泽、杰、豪、雨、梓、欣、子、思、蔼、仁、容、德、" \
            "轩、贤、良、伦、正、清、义、诚、直、道、颖、灵、睿、锐、哲、慧、敦、迪、明、晓、显、悉、晰、维、学、思、悟、" \
            "析、文、书、勤、俊、威、英、健、壮、焕、挺、秀、伟、武、雄、巍、松、柏、山、石、" \
            "婵、娟、姣、妯、婷、姿、媚、婉、妩、倩、兰、达、耀、兴、荣、华、旺、盈、丰、余、昌、盛"
first_name_list = [item for item in first_name.split('、')]
last_name_list = [item for item in last_name.split('、')]
sno = 8002119000
clsno1 = 1
clsno2 = 1
clsno3 = 1
provinces = json.load(open('provinces.json', encoding='utf-8'))
cities = json.load(open('cities.json', encoding='utf-8'))

template = "into student values('%s','%s','%s','%s','%s','%s',to_date('%d-%d-%d','yyyy-mm-dd')," \
           "to_date('%d','yyyy'),'%s','%s','1234567ab')\n"


def get_firstName():
    index = math.floor(random() * len(first_name_list))
    return first_name_list[index]


def get_lastName():
    tag = random() > 0.5
    index = math.floor(random() * len(last_name_list))
    if tag:
        return last_name_list[index]
    else:
        lastName = last_name_list[index]
        index = math.floor(random() * len(last_name_list))
        return lastName + last_name_list[index]
    pass


def get_sname():
    return get_firstName().strip() + get_lastName().strip()


def get_year():
    return math.floor(random() * 5 + 1998)


def get_month():
    return math.floor(random() * 12 + 1)


def get_day():
    return math.floor(random() * 28 + 1)


def get_mobile():
    st = ['15', '17', '18', '13']
    mobile = st[math.floor(random() * len(st))]
    for i in range(9):
        mobile += str(math.floor(random() * 10))
    return mobile


def get_classno():
    global clsno1, clsno2, clsno3
    clsno = (clsno1, clsno2, clsno3)
    clsno3 += 1
    if clsno3 == 4:
        clsno3 = 1
        clsno2 += 1
    if clsno2 == 4:
        clsno2 = 1
        clsno1 += 1
    return clsno


def get_sno():
    global sno
    sno += 1
    return sno


def get_sex():
    if random() > 0.5:
        return '女'
    else:
        return '男'


def get_province():
    index = math.floor(random() * len(provinces))
    return provinces[index]


def get_city(pcode):
    plist = []
    for item in cities:
        if pcode == item['provinceCode']:
            plist.append(item)
    index = math.floor(random() * len(plist))
    return plist[index]


def get_natnm():
    province = get_province()
    if province['code'] == '11' or province['code'] == '12' or province['code'] == '50' or province['code'] == '31':
        return province['name']
    else:
        return province['name'] + '-' + get_city(province['code'])['name']


if __name__ == '__main__':
    '''
    生成sql
    '''
    sql = open('STUDENT.sql', "w", encoding='utf-8')
    sql.write("insert all\n".upper())
    for i in range(90):
        clsno = "ND%02d%02d%02d" % get_classno()
        mno = clsno[0:6]
        dno = mno[0:4]
        year = get_year()
        month = get_month()
        day = get_day()
        if dno != 'ND04':
            continue
        for j in range(30):
            statement = template % (
                get_sno(), clsno, mno, dno, get_sname(), get_sex(), year, month, day, 2019, get_mobile(),
                get_natnm())
            sql.write(statement.upper())
    sql.write("select * from dual;".upper())
    sql.close()
